package cn.xdd.dao;

import cn.xdd.dbutil.DbUtil;
import cn.xdd.entity.User;
import cn.xdd.entity.power;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @author user_xdd
 * @Date 2022-03-27 13:59
 * @description
 * @return
 */
public class UserDao {
    private static final Logger LOGGER = Logger.getLogger(UserDao.class);



//    返回一个用户所有的权限包括个人权限
    public List<power> getTokenByID(int id) throws SQLException {

        Connection conn = DbUtil.getDataSource().getConnection();

        //创建SQL执行工具
        QueryRunner queryRunner = new QueryRunner();
        String sql = "SELECT\n" +
                "\tpower.* \n" +
                "FROM\n" +
                "\tusers\n" +
                "\tINNER JOIN t_user_role ON users.user_id = t_user_role.ur_uid\n" +
                "\tINNER JOIN t_role_power\n" +
                "\tINNER JOIN role ON t_role_power.rp_rid = role.role_id \n" +
                "\tAND t_user_role.ur_rid = role.role_id\n" +
                "\tINNER JOIN power ON t_role_power.rp_pid = power.power_id \n" +
                "WHERE\n" +
                "\tusers.user_id = ? \n" +
                "GROUP BY\n" +
                "\tpower.power_powerCard";
        List<power> queryResultPower = queryRunner.query(conn, sql, new BeanListHandler<>(power.class), id);
         LOGGER.debug("    queryResultPower:"+queryResultPower);
conn.close();
         return queryResultPower;

    };

    public User GetUserById(int id) throws SQLException {

        Connection conn = DbUtil.getDataSource().getConnection();

        //创建SQL执行工具
        QueryRunner queryRunner = new QueryRunner();
        String sql = "SELECT\n" +
                "\tusers.* \n" +
                "FROM\n" +
                "\tusers \n" +
                "WHERE\n" +
                "\tusers.user_id = ?";
        User queryResultUser = queryRunner.query(conn, sql, new BeanHandler<>(User.class), id);
         LOGGER.debug("    queryResultUser:"+queryResultUser);
        conn.close();
        return queryResultUser;
    }


    public List<Integer> getRoleByID(int id) throws SQLException{

        Connection conn = DbUtil.getDataSource().getConnection();

        //创建SQL执行工具
        QueryRunner queryRunner = new QueryRunner();
        String sql = "SELECT\n" +
                "\tt_user_role.ur_rid\n" +
                "FROM\n" +
                "\tusers\n" +
                "\tINNER JOIN\n" +
                "\tt_user_role\n" +
                "\tON \n" +
                "\t\tusers.user_id = t_user_role.ur_uid";
        List<Integer> queryResultRole = queryRunner.query(conn, sql, new BeanListHandler<>(Integer.class), id);
         LOGGER.debug("    queryResultRole:"+queryResultRole);
        conn.close();
        return queryResultRole;
    }

    public List<power> getPowerByRID(int id) throws SQLException{
      return  null;
    }



    public User login(String email, String password) throws SQLException {

        Connection conn = DbUtil.getDataSource().getConnection();

        //创建SQL执行工具
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from  users  where user_email=? and user_password=? ;";
        User queryResultUser = queryRunner.query(conn, sql, new BeanHandler<>(User.class), email, password);
        LOGGER.debug("    queryResultUser:" + queryResultUser);

        conn.close();
        return queryResultUser;
    }

    public boolean checkUserName(String name) throws SQLException {
        boolean flag=true;
        Connection conn = DbUtil.getDataSource().getConnection();

        //创建SQL执行工具
        QueryRunner queryRunner = new QueryRunner();

        String sql = "select * from users where user_nickname = ?";
        User query = queryRunner.query(conn, sql, new BeanHandler<>(User.class), name);
        LOGGER.debug("    query:" + query);

        if(query==null)
            flag=false;


        conn.close();
        return flag;
    }
    public boolean checkUserEmail(String email) throws SQLException {
        boolean flag=true;
        Connection conn = DbUtil.getDataSource().getConnection();

        //创建SQL执行工具
        QueryRunner queryRunner = new QueryRunner();

        String sql = "select * from users where user_email =  ?";
        User query = queryRunner.query(conn, sql, new BeanHandler<>(User.class), email);
        LOGGER.debug("    query:" + query);

        if(query==null)
            flag=false;


        conn.close();
        return flag;
    }

    public boolean checkUserTele(String Tele) throws SQLException {
        boolean flag=true;
        Connection conn = DbUtil.getDataSource().getConnection();

        //创建SQL执行工具
        QueryRunner queryRunner = new QueryRunner();

        String sql = "select * from users where user_phone =  ?";
        User query = queryRunner.query(conn, sql, new BeanHandler<>(User.class), Tele);
        LOGGER.debug("    query:" + query);

        if(query==null)
            flag=false;


        conn.close();
        return flag;
    }
    public boolean updateLoginUser(User user) throws SQLException {
        boolean flag=true;
        Connection conn = DbUtil.getDataSource().getConnection();

        //创建SQL执行工具
        QueryRunner queryRunner = new QueryRunner();

        String sql = "update users\n" +
                "set  user_nickname=?,user_phone=?,user_email=?,user_password=?\n" +
                "where user_id=?";
        int i = queryRunner.update(conn, sql,user.getUser_nickname(),user.getUser_phone(),user.getUser_email(),user.getUser_password(),user.getUser_id());


        if(i==0)
            flag=false;
         LOGGER.debug("    flag:"+flag);


        conn.close();
        return flag;
    }

    public boolean addUser(User user) throws SQLException{
        boolean flag=true;
        Connection conn = DbUtil.getDataSource().getConnection();

        //创建SQL执行工具
        QueryRunner queryRunner = new QueryRunner();

        String sql = "insert into bookmanage2.users (user_nickname,user_phone,user_email,user_password,user_sex)\n" +
                "values (?,?,?,?,?);";
//        把user插入数据库
        int i=queryRunner.update(conn,sql,user.getUser_nickname(),user.getUser_phone(),user.getUser_email(),user.getUser_password(),user.getUser_sex());
        if(i==0)
            flag=false;
//        把插入的user拿出来
        User newUser=login(user.getUser_email(),user.getUser_password());
        int id=newUser.getUser_id();
        if(id!=0){
//            给他一个默认的读者角色
            sql="insert into t_user_role (ur_uid, ur_rid)values (?,30);";
            i=queryRunner.update(conn,sql,id);
        }else {
            flag=false;
        }
        if(i==0)
            flag=false;


//        insert into role (role_id, role_commit)values ();



         LOGGER.debug("    flag:"+flag);
        conn.close();
        return flag;
    }


}
